<?php
/**
 * Created by PhpStorm.
 * User: fengdh
 * Date: 2019/9/4
 * Time: 10:01
 */

namespace business\procurement\services;

use business\base\services\DictionaryService;
use business\elasticsearch\types\RecruitItemType;
use business\procurement\models\BiddingModel;
use business\procurement\models\BiddingAnnouncementCategoriesModel;
use business\procurement\models\BiddingServiceAreaModel;
use business\procurement\models\CzSaasModel;
use business\procurement\models\RecruitItemModel;
use business\search\services\BaseUtil;
use common\utils\DateTimeUtils;
use common\utils\PinyinUtil;
use yii\mongodb\Query;

class ProcurementService
{
    private static $database = "b2b_bidding";
    private static $bidDetailCollection = "BiddingDetail";

    public function getRecruitIdsList($time = null)
    {
        $query = RecruitItemModel::find()->select(["item_id"]);
        $time && $query->andWhere(['>', 'last_modify_time', $time])->orWhere(['and', ['<', 'register_end_date', DateTimeUtils::now()], ['>', 'register_end_date', $time]]);
        $data = $query->column();
        return $data;
    }

    public function getBiddingIdsList($time = null)
    {
        $query = BiddingModel::find()->select(["uid"]);
        $time && $query->andWhere(['>', 'last_modified_time', $time])->orWhere(['and', ['<', 'register_end_date', DateTimeUtils::now()], ['>', 'register_end_date', $time]]);
        $data = $query->column();

        $query = BiddingAnnouncementCategoriesModel::find()->select(["bid_announcement_uid"]);
        $time && $query->Where(['>', 'last_modified_time', $time]);
        $data2 = $query->column();
        foreach ($data2 as $id) {
            if (!in_array($id, $data)) {
                array_push($data, $id);
            }
        }
        return $data;
    }

    public function getRecruitBasicInfo($recruit_id)
    {
        $sql = <<<sql
            SELECT
                ti.item_id,
                ti.recruit_id,
                ti.name,
                ti.register_condition,
                ti.order_num,
                ti.categories,
                ti.service_quals as service_qualifies,
                ti.service_areas,
                ti.company_area,
                ti.register_fund,
                ti.build_years,
                ti.case_num,
                ti.state,
                ti.subject,
                ti.publish_time,
                ti.company_id,
                ti.register_end_date,
                ti.create_time,
                ti.last_modify_time,
                ti.supplier_type,
                ti.merit,
                ti.covered_area,
                ti.item_type,
                ti.source,
                GROUP_CONCAT(DISTINCT tia.service_area) AS zmAreaCodes
            FROM
                b2b_bidding.bid_recruit_item ti
            LEFT JOIN
                b2b_bidding.bid_recruit_item_area tia ON ti.item_id = tia.item_id
            WHERE
                ti.item_id = :recruitId
sql;
        $sqlParams[':recruitId'] = $recruit_id;
        $recruit = \Yii::$app->db->createCommand($sql, $sqlParams)->queryOne();
        $basicInfo['sjId'] = "recruit_" . $recruit_id;
        $basicInfo['sjType'] = "recruit";
        $basicInfo['sjState'] = $recruit['state'] == 0 || $recruit['state'] == 2 || $recruit['state'] == 3 ? false : true;
        $basicInfo['sjDeveloperId'] = !empty($recruit) ? $recruit['company_id'] : '';
        //商机标题
        $basicInfo['sjTitle'] = !empty($recruit) ? $recruit['subject'] : '';
        $basicInfo['sjShortTitle'] = $basicInfo['sjTitle'];
        $basicInfo['sjTitlePinyin'] = $this->getPinyin($basicInfo['sjTitle']);
        $basicInfo['sjSource'] = !empty($recruit) ? $recruit['source'] : '';
        //基础分类
        $categoriesInfo = $this->getProcurementBasicCategoryCode($recruit['categories']);
        $basicInfo = array_merge($basicInfo, $categoriesInfo);
        //商机发布时间
        $basicInfo['sjPublishDate'] = DateTimeUtils::convertToGmtTime($recruit['publish_time']);
        //商机发布时间—时间戳
        $basicInfo['sjPublishDateTimestamp'] = strtotime($recruit['publish_time']) ?: 0;
        //商机截止时间
        $basicInfo['sjDueDate'] = DateTimeUtils::convertToGmtTime($recruit['register_end_date']);
        //商机截止时间—时间戳
        $basicInfo['sjDueDateTimestamp'] = strtotime($recruit['register_end_date']) ?: 0;
        //商机是否已截止
        $basicInfo['sjIsExpired'] = strtotime($recruit['register_end_date']) > DateTimeUtils::time() ? 0 : 1;
        $locationInfo = $this->getProcurementLocation($recruit['zmAreaCodes']);
        $basicInfo = array_merge($basicInfo, $locationInfo);
        //商机注册资本
        $basicInfo['sjRegisterFund'] = !empty($recruit) ? $recruit['register_fund'] : 0;
        //商机案例数量
        $basicInfo['sjCaseNum'] = !empty($recruit) ? intval($recruit['case_num']) : 0;
        //商机服务资质要求
        $qualifiesInfo = $this->getProcurementQualify($recruit['service_qualifies']);
        $basicInfo = array_merge($basicInfo, $qualifiesInfo);
        //商机服务区域
        // company_area才是服务区域，service_areas是公司所在地，实际上是反的，不知道为什么，后面的人多注意（此处注释copy自原solr的注释）
        //$basicInfo['sjServiceAreaCode'] = explode(",", $recruit['company_area']);
        //$basicInfo['sjServiceAreaName'] = $this->getServiceAreaNames($recruit['company_area']);
        $basicInfo['sjServiceAreaCode'] = explode(",", $recruit['service_areas']);
        $basicInfo['sjServiceAreaName'] = $this->getServiceAreaNames($recruit['service_areas']);
        //商机供应商类型要求
        $basicInfo['sjSupplierType'] = !empty($recruit['supplier_type']) ? $recruit['supplier_type'] : '';
        //商机优势
        $basicInfo['sjMerit'] = !empty($recruit['merit']) ? $recruit['merit'] : '';
        //商机建筑面积
        $basicInfo['sjCoveredArea'] = !empty($recruit) ? intval($recruit['covered_area']) : 0;
        //商机招募类型
        $basicInfo['sjItemType'] = !empty($recruit['item_type']) && isset(RecruitItemType::$map[$recruit['item_type']]) ?
            RecruitItemType::$map[$recruit['item_type']] : RecruitItemType::$map[RecruitItemType::OTHER];
        //商机报名所有条件(项目所在地，注册资本，案例数)
        $sjRegisterCondition = [];
        if (!empty($basicInfo["sjServiceAreaName"])) {
            if (is_array($basicInfo["sjServiceAreaName"])) {
                $projectLocationName = implode(",", $basicInfo["sjServiceAreaName"]);
            } else {
                $projectLocationName = $basicInfo["sjServiceAreaName"];
            }
            array_push($sjRegisterCondition, $projectLocationName);
        }
        if (!empty($basicInfo['sjRegisterFund']) && $basicInfo['sjRegisterFund'] != 0) {
            $registerFund = '注册资本' . $basicInfo['sjRegisterFund'] . '万';
            array_push($sjRegisterCondition, $registerFund);
        }
        if (!empty($basicInfo['sjCaseNum']) && $basicInfo['sjCaseNum'] != 0) {
            $caseNum = '至少' . $basicInfo['sjCaseNum'] . '个案例';
            array_push($sjRegisterCondition, $caseNum);
        }
        $basicInfo['sjRegisterCondition'] = implode("|", $sjRegisterCondition);

        return $basicInfo;
    }

    public function getBiddingBasicInfo($bidding_id)
    {
        $sql = <<<sql
            SELECT ta.uid AS id,
                ta.operate_state as operate_state,
                ta.developer_uid as developer_uid,
                ta.developer_uid AS companyId,
                ta.title AS title,
                ta.short_title AS shortTitle,
                ta.procurement_type AS procurementTypeValue,
                ta.register_end_date AS registerEndDate,
                ta.state AS stateValue,
                ta.created_time AS publishTime,
                ta.finalist_time AS shortlistEndTime,
                ta.complete_winbid_time AS fixEndTime,
                GROUP_CONCAT(tb.category_code) AS serviceCategoryCodes,
                ta.is_valid as validValue,
                ta.created_by AS createdBy,
                ta.created_time AS createdTime,
                ta.last_modified_by AS lastModifiedBy,
                ta.last_modified_time AS lastModifiedTime,
                ta.is_support_online AS isSupportOnline,
                ta.audit_time As auditTime,
                ta.source_type As sourceType,
                (SELECT GROUP_CONCAT(DISTINCT tt.service_area) FROM b2b_bidding.bid_announcements_area tt WHERE tt.bid_announcement_uid = ta.uid) as city,
                (SELECT GROUP_CONCAT(DISTINCT tt.project_id) FROM b2b_bidding.bid_announcements_project tt WHERE tt.bid_announcement_uid = ta.uid) as projectId
		    FROM
		        b2b_bidding.bid_announcements ta
		    LEFT JOIN
		        b2b_bidding.bid_announcement_categories tb ON ta.uid = tb.bid_announcement_uid
		    WHERE
		        ta.uid = :biddingId
sql;
        $sqlParams = [':biddingId' => $bidding_id];
        $biddingInfo = \Yii::$app->db->createCommand($sql, $sqlParams)->queryOne();
        if (empty($biddingInfo)) {
            return [];
        }
        $basicInfo['sjId'] = "bidding_" . $bidding_id;
        $basicInfo['sjType'] = "bidding";
        $basicInfo['sjState'] = $biddingInfo['operate_state'] == 2 && $biddingInfo['procurementTypeValue'] == 0  ? true : false;
        $basicInfo['sjDeveloperId'] = !empty($biddingInfo) ? $biddingInfo['developer_uid'] : '';
        //商机标题
        $basicInfo['sjTitle'] = !empty($biddingInfo) ? htmlspecialchars($biddingInfo['title']) : '';
        $basicInfo['sjShortTitle'] = !empty($biddingInfo) ? htmlspecialchars($biddingInfo['shortTitle']) : '';
        $basicInfo['sjTitlePinyin'] = $this->getPinyin($basicInfo['sjTitle']);
        $basicInfo['sjSource'] = !empty($biddingInfo) ? $biddingInfo['sourceType'] : '';
        //基础分类
        $categoryCode = trim($biddingInfo['serviceCategoryCodes']) ? explode(',', $biddingInfo['serviceCategoryCodes']) : [];
        $categoriesInfo = $this->getProcurementBasicCategoryCode($categoryCode);
        $basicInfo = array_merge($basicInfo, $categoriesInfo);
        //商机发布时间
        $basicInfo['sjPublishDate'] = DateTimeUtils::convertToGmtTime($biddingInfo['createdTime']);
        //商机发布时间—时间戳
        $basicInfo['sjPublishDateTimestamp'] = strtotime($biddingInfo['createdTime']) ?: 0;
        //商机截止时间
        $basicInfo['sjDueDate'] = DateTimeUtils::convertToGmtTime($biddingInfo['registerEndDate']);
        //商机截止时间—时间戳
        $basicInfo['sjDueDateTimestamp'] = strtotime($biddingInfo['registerEndDate']) ?: 0;
        //商机是否已截止
        $basicInfo['sjIsExpired'] = (intval($biddingInfo['stateValue'])==2 && strtotime($biddingInfo['registerEndDate'])>DateTimeUtils::time()) ? 0 : 1;
        //商机项目所在地(sjProjectLocationName,sjProjectLocationCode)
        $locationInfo = $this->getProcurementLocationByBidding($bidding_id);
        $basicInfo = array_merge($basicInfo, $locationInfo);
        $condition = $this->getDetailConditionByBiddingId($bidding_id);
        //商机注册资本
        $basicInfo['sjRegisterFund'] = !empty($condition['scRegCapital']) ? $condition['scRegCapital'] : 0;
        //商机案例数量
        $basicInfo['sjCaseNum'] = !empty($condition['scCaseNum']) ? intval($condition['scCaseNum']) : 0;
        //商机服务资质要求
        $qualifiesInfo = $this->getProcurementQualify(!empty($condition['qualify']) ? $condition['qualify'] : '');
        $basicInfo = array_merge($basicInfo, $qualifiesInfo);
        //商机服务区域
        $basicInfo['sjServiceAreaCode'] = !empty($condition['scServiceAreaCityCode']) ? $condition['scServiceAreaCityCode'] : [];
        $basicInfo['sjServiceAreaName'] = !empty($condition['scServiceAreaName']) ? $condition['scServiceAreaName'] : [];
        //商机供应商类型要求
        $basicInfo['sjSupplierType'] = '';
        //商机优势
        $basicInfo['sjMerit'] = '';
        //商机建筑面积
        $basicInfo['sjCoveredArea'] = 0;

        //商机报名所有条件(项目所在地，注册资本，案例数)
        $sjRegisterCondition = [];
        if (!empty($basicInfo["sjServiceAreaName"])) {
            if (is_array($basicInfo["sjServiceAreaName"])) {
                $projectLocationName = implode(",", $basicInfo["sjServiceAreaName"]);
            } else {
                $projectLocationName = $basicInfo["sjServiceAreaName"];
            }
            array_push($sjRegisterCondition, $projectLocationName);
        }
        if (!empty($basicInfo['sjRegisterFund']) && $basicInfo['sjRegisterFund'] != 0) {
            $registerFund = '注册资本' . $basicInfo['sjRegisterFund'] . '万';
            array_push($sjRegisterCondition, $registerFund);
        }
        if (!empty($basicInfo['sjCaseNum']) && $basicInfo['sjCaseNum'] != 0) {
            $caseNum = '至少' . $basicInfo['sjCaseNum'] . '个案例';
            array_push($sjRegisterCondition, $caseNum);
        }
        $basicInfo['sjRegisterCondition'] = implode("|", $sjRegisterCondition);

        return $basicInfo;
    }


    /**
     * 获取采招sass信息
     * @param $developerId
     * @return array
     * @throws \yii\db\Exception
     */
    public function getCzSassDeveloperInfo($developerId)
    {

        $record = CzSaasModel::findOne([
           'developer_id'=> $developerId
        ]);
        $czSassInfo = [];
        if (!empty($record)) {
            $czSassInfo = $record->toArray();
        }
        $result = [];
        if (!empty($czSassInfo)) {
            $result['tenantId'] = $czSassInfo['tenant_id'] ?? '';
        }
        return $result;
    }

    public function getProcurementDeveloperInfo($developerId)
    {
        $sql = <<<sql
            SELECT
				s.company_name,
				s.short_name,
				s.company_logo,
				s.company_honor,
				s.company_image,
				s.developer_level,
				s.province_code,
				s.developer_type,
                s.enterprise_nature
            FROM
                b2b_company.uuc_developer s
            where
                s.developer_id = :developerId
sql;
        $sqlParams = [':developerId' => $developerId];
        $developer = \Yii::$app->db->createCommand($sql, $sqlParams)->queryOne();

        $developerInfo['sjDeveloperName'] = !empty($developer) ? $developer['company_name'] : '';
        $developerInfo['sjDeveloperShortName'] = !empty($developer) ? $developer['short_name'] : '';
        $developerInfo['sjDeveloperLogo'] = !empty($developer) ? $developer['company_logo'] : '';
        $developerInfo['sjDeveloperHonor'] = !empty($developer) ? $developer['company_honor'] : '';
        $developerInfo['sjDeveloperLevel'] = !empty($developer) ? intval($developer['developer_level']) : 0;
        $developerInfo['sjDeveloperLocationCode'] = !empty($developer) ? $developer['province_code'] : '';
        $developerInfo['sjDeveloperType'] = !empty($developer) ? $developer['developer_type'] : '';
        $developerInfo['enterpriseNature'] = !empty($developer) ? $developer['enterprise_nature'] : 0;
        //是否五天内首次发布招募
        $developerInfo['isFirstPublishSj'] = $this->isFirstPublishRecruitIn5Days($developerId);
        //7天内处理入库次数
        $developerInfo['handleStorageCount'] = $this->getHandleStorageCountIn7Days($developerId);

        return $developerInfo;
    }

    /**
     * 根据招标ID获取招标详情信息
     * @param $bidId
     * @return array|bool|null
     */
    public function getBidDetail($bidId)
    {
        if (empty($bidId)) {
            return null;
        }
        $query = new Query();
        $bidDetail = $query->from([self::$database, self::$bidDetailCollection])->andFilterWhere(["_id" => $bidId])->one();
        return $bidDetail;
    }

    /**
     * 5天内是否首次发布招募
     * @param $developerId
     * @return int
     */
    private function isFirstPublishRecruitIn5Days($developerId)
    {
        $sql = <<<sql
            SELECT
                a.publish_time
            FROM
                b2b_bidding.bid_recruit_item a
            WHERE
                a.company_id = :developerId
            ORDER BY
                a.publish_time
            LIMIT 1
sql;
        $sqlParams = [':developerId' => $developerId];
        $firstPublishTime = \Yii::$app->db->createCommand($sql, $sqlParams)->queryScalar();
        if (!empty($firstPublishTime) && strtotime($firstPublishTime) > strtotime("-5 day")) {
            return 1;
        }
        return 0;
    }

    /**
     * 7天内处理入库次数
     * @param $developerId
     * @return false|null|string
     */
    private function getHandleStorageCountIn7Days($developerId)
    {
        $sql = <<<sql
            SELECT
                COUNT(1)
            FROM b2b_bidding.bid_recruit_item_register a
            INNER JOIN b2b_bidding.bid_recruit_item b
            ON a.item_id = b.item_id
            WHERE
                b.company_id = :developerId
                AND (a.`storage` = 1 OR a.`storage` = 3 OR a.`storage` = 4)
                AND a.storage_time > date_sub(curdate(), INTERVAL 7 DAY)
sql;
        $sqlParams = [':developerId' => $developerId];
        return \Yii::$app->db->createCommand($sql, $sqlParams)->queryScalar();
    }

    /**
     * 获取指定字符串的全拼和简拼
     * @param $word
     * @return array
     */
    private function getPinyin($word)
    {
        $pinyin = PinyinUtil::getPinyin($word);
        $shortPinyin = PinyinUtil::getShortPinyin($word);
        return [$pinyin, $shortPinyin];
    }

    /**
     * 根据codes获取所有3层的basicCategory和OperationCategory
     * @param $category_code
     * @return array
     */
    private function getProcurementBasicCategoryCode($category_code)
    {
        $result = [];
        $categories = BaseUtil::getAllBasicCategoriesByCodes($category_code);
        if (is_array($categories)) {
            foreach ($categories as $key => $value) {
                $result[$key] = $value;
            }
        }
        return $result;
    }

    public function getProcurementLocationByBidding($biddingId)
    {
        $sql = <<<sql
            SELECT
                GROUP_CONCAT(DISTINCT service_area) AS service_area
            FROM
                b2b_bidding.bid_announcements_area
            WHERE
                bid_announcement_uid = :biddingId
sql;
        $sqlParams = [':biddingId' => $biddingId];
        $areaCodes = \Yii::$app->db->createCommand($sql, $sqlParams)->queryColumn();
        $serviceAreas = implode(',', $areaCodes);
        return $this->getProcurementLocation($serviceAreas);
    }

    /**
     * 招募项目所在地
     * @param $serviceAreas
     * @return array
     */
    private function getProcurementLocation($serviceAreas)
    {
        $recruitBasic = [
            'sjProjectLocationCode' => [],
            'sjProjectLocationName' => [],
            'sjSearchProjectLocationCode' => [],
        ];
        //1.如果选择的是全国，那么直接返回全国
        if (empty($serviceAreas)) {
            return $recruitBasic;
        }
        if (count($serviceAreas) == 1 && strtolower($serviceAreas) == 'china') {
            $recruitBasic['sjProjectLocationCode'] = $serviceAreas;
            $recruitBasic['sjProjectLocationName'] = '全国';
            $recruitBasic['sjSearchProjectLocationCode'][] = (string)$serviceAreas;
        } else {
            $zmAreaCodeList = explode(',', $serviceAreas);
            $diction = new DictionaryService();
            if (empty($zmAreaCodeList)) {
                return $recruitBasic;
            } else {
                foreach ($zmAreaCodeList as $k => $v) {
                    $region = $diction->getRegionByCode($v);
                    if (empty($region)) {
                        continue;
                    }
                    !in_array((string)$v, $recruitBasic['sjProjectLocationCode']) && array_push($recruitBasic['sjProjectLocationCode'], (string)$v);
                    !in_array($region->name, $recruitBasic['sjProjectLocationName']) && array_push($recruitBasic['sjProjectLocationName'], $region->name);
                    !in_array((string)$v, $recruitBasic['sjSearchProjectLocationCode']) && array_push($recruitBasic['sjSearchProjectLocationCode'], (string)$v);
                    if (!empty($region->parentCode)) {
                        !in_array((string)$region->parentCode, $recruitBasic['sjSearchProjectLocationCode']) && array_push($recruitBasic['sjSearchProjectLocationCode'], (string)$region->parentCode);
                    }
                }
//                //去重
//                if (is_array($recruitBasic['sjSearchProjectLocationCode'])) {
//                    $recruitBasic['sjSearchProjectLocationCode'] = array_flip($recruitBasic['sjSearchProjectLocationCode']);
//                    $recruitBasic['sjSearchProjectLocationCode'] = array_flip($recruitBasic['sjSearchProjectLocationCode']);
//                }
            }
        }

        return $recruitBasic;
    }

    /**
     * 构建资质-等级名称字符串
     * @param $serviceQualifies
     * @return string
     */
    private function getProcurementQualify($serviceQualifies)
    {
        $result = $serviceQualifyList = $codes = $levels = [];
        $qualifyNameBuffer = "";
        if (!empty($serviceQualifies)) {
            $serviceQualifyList = explode(',', $serviceQualifies);
        }
        if (empty($serviceQualifyList)) {
            return [];
        } else {
            foreach ($serviceQualifyList as $item) {
                $qualifies = explode('-', $item);
                array_push($codes, $qualifies[0]);
                if (count($qualifies) > 1) {
                    array_push($levels, $qualifies[1]);
                }
            }
            if (empty($codes)) {
                return "";
            }
            $sjQualificationCode = [];
            for ($i = 0; $i < count($codes); $i++) {
                $qualify = $this->getQualificationByCode($codes[$i]);
                if (!empty($qualify)) {
                    $qualifyNameBuffer .= $qualify['qualification_name'];
                }

                if (isset($levels[$i])) {
                    $level = $this->getQualificationLevelById($levels[$i]);

                    if (!empty($level)) {
                        $qualifyNameBuffer .= "-" . $level['level_name'];
                    }

                    $qualifyNameBuffer .= ",";
                    array_push($sjQualificationCode, $codes[$i] . "-" . $levels[$i]);
                    $result["sjQualification_" . $codes[$i]] = $level['priority'];
                }

            }
            $result['sjQualificationCode'] = $sjQualificationCode;
            $result['sjQualificationName'] = substr($qualifyNameBuffer, 0, -1);
            return $result;
        }
    }

    /**
     * 根据资质码查询资质
     * @param $qualCode
     * @return array|false
     */
    private function getQualificationByCode($qualCode)
    {
        $sql = <<<sql
            SELECT
                bsp_qualification.id,
                bsp_qualification.qualification_code,
                bsp_qualification.qualification_name,
                bsp_qualification.`status`,
                bsp_qualification.is_relation_basic,
                bsp_qualification.create_time,
                bsp_qualification.update_time
            FROM
                b2b_parameter.bsp_qualification
            WHERE
                 bsp_qualification.qualification_code = :code
sql;
        $sqlParams[':code'] = $qualCode;
        return \Yii::$app->db->createCommand($sql, $sqlParams)->queryOne();
    }

    /**
     * 根据资质等级id查询资质等级对象
     * @param $levelCode
     * @return array|false
     */
    private function getQualificationLevelById($levelCode)
    {
        $sql = <<<sql
            SELECT
                level_code,
                level_name,
                priority
            FROM
                b2b_parameter.bsp_qualification_level
            WHERE
                id = :id
sql;
        $sqlParams[':id'] = $levelCode;
        return \Yii::$app->db->createCommand($sql, $sqlParams)->queryOne();
    }

    /**
     * 获取招募服务区域
     * @param $serviceAreas
     * @return string
     */
    private function getServiceAreaNames($serviceAreas)
    {
        $serviceAreasList = [];
        if (!empty($serviceAreas)) {
            $serviceAreasList = explode(',', $serviceAreas);
        }
        if (empty($serviceAreasList)) {
            return [];
        }
        if (count($serviceAreasList) == 1 && $serviceAreasList[0] == "china") {
            return ["全国"];
        }
        $regionNames = [];
        $diction = new DictionaryService();
        foreach ($serviceAreasList as $key => $value) {
            $region = $diction->getRegionByCode($value);

            if (!empty($region->name)) {
                array_push($regionNames, $region->name);
            }
        }
        return $regionNames;
    }

    /**
     * 根据招标id获取详细的招标条件
     * @param $biddingId
     * @return array
     */
    public function getDetailConditionByBiddingId($biddingId)
    {
        $sql = <<<sql
            SELECT
                brc.uid,
                bcs.bid_announcement_uid,
                brc.register_fund,
                brc.project_count,
                brc.is_limit_service_area_case,
                brc.provide_organization_code,
                brc.provide_tax_registration,
                brc.provider_qualification,
                brc.provider_main_business,
                brc.provider_company_contact,
                brc.provider_brand,
                brc.provider_nearly_case,
                brc.provider_enterprise_honor,
                GROUP_CONCAT(DISTINCT brq.qualify_name_code) AS qualify_name_codes,
                GROUP_CONCAT(DISTINCT brq.qualify_level_code) AS qualify_level_codes,
                GROUP_CONCAT(DISTINCT bsa.province_code) AS province_codes,
                GROUP_CONCAT(DISTINCT bsa.city_code) AS city_codes,
                GROUP_CONCAT(DISTINCT bcs.category_code) AS category_codes
            FROM
                b2b_bidding.bid_announcement_categories bcs
            LEFT JOIN
                b2b_bidding.bid_ann_register_condition brc ON brc.bid_announcement_uid = bcs.bid_announcement_uid
            LEFT JOIN
                b2b_bidding.bid_ann_register_condition_qualify brq ON brc.bid_announcement_uid = brq.bid_announcement_uid
            LEFT JOIN
                b2b_bidding.bid_announcements_service_area bsa ON bsa.bid_announcement_uid = brc.bid_announcement_uid
            WHERE
                bcs.bid_announcement_uid = :biddingId
sql;
        $sqlParams = [':biddingId' => $biddingId];
        $basicCondition = \Yii::$app->db->createCommand($sql, $sqlParams)->queryOne();

        $registerCondition = [];
        if (!empty($basicCondition)) {
            //注册资金
            $registerCondition['scRegCapital'] = !empty($basicCondition['register_fund'])
                ? $basicCondition['register_fund'] : 0;

            //成立年限(暂无此条件)
            $registerCondition['scBuildYears'] = 0;

            //案例数量
            $registerCondition['scCaseNum'] = !empty($basicCondition['project_count'])
                ? $basicCondition['project_count'] : 0;

            //案例是否限制区域
            $registerCondition['scIsLimitServiceAreaCase'] = $basicCondition['is_limit_service_area_case'];

            //服务产品分类
            $registerCondition["scCategoryCode"] = !empty($basicCondition['category_codes'])
                ? explode(',', $basicCondition['category_codes']) : [];
            $registerCondition['scCategoryName'] = $this->getBiddingCategory($biddingId);

            //服务资质
            $registerCondition['scQualificationCode'] = !empty($basicCondition['qualify_name_codes'])
                ? explode(',', $basicCondition['qualify_name_codes']) : [];
            $registerCondition['scQualificationName'] = $this->getBiddingQualifyNamesById($biddingId);

            //服务区域
            $registerCondition['scServiceAreaCode'] = !empty($basicCondition['province_codes'])
                ? explode(',', $basicCondition['province_codes']) : [];
            $registerCondition['scServiceAreaCityCode'] = !empty($basicCondition['city_codes'])
                ? explode(',', $basicCondition['city_codes']) : [];
            $registerCondition["scServiceAreaName"] = $this->getBiddingServiceAreaNamesById($biddingId);
            $qualifyLevelCodes = !empty($basicCondition['qualify_level_codes'])
                ? explode(',', $basicCondition['qualify_level_codes']) : [];
            $qualifyArray = [];
            foreach ($registerCondition['scQualificationCode'] as $key => $val) {
                if (isset($qualifyLevelCodes[$key]) && isset($registerCondition['scQualificationCode'][$key])) {
                    $qualifyArray[] = $qualifyLevelCodes[$key] . '-' . $registerCondition['scQualificationCode'][$key];
                }
            }
            $registerCondition["qualify"] = implode(",", $qualifyArray);
        }

        return $registerCondition;
    }

    /**
     * 获取服务产品分类
     * @param string $biddingId
     * @return array
     */
    public function getBiddingCategory($biddingId)
    {

        $sql = <<<sql
            SELECT
                b.category_name
            FROM
                b2b_bidding.bid_announcement_categories a,
                b2b_parameter.bsp_basic_forest b
            WHERE
                a.bid_announcement_uid = :biddingId AND
                a.category_code = b.category_code AND
                b.category_status = 1
sql;
        $sqlParams[":biddingId"] = $biddingId;

        return \Yii::$app->db->createCommand($sql, $sqlParams)->queryColumn();
    }

    /**
     * 获取服务资质名称
     * @param $biddingId
     * @return array
     */
    public function getBiddingQualifyNamesById($biddingId)
    {
        $sql = <<<sql
            SELECT
                CONCAT(b.qualification_name,c.level_name)
            FROM
                b2b_bidding.bid_ann_register_condition_qualify a,
                b2b_parameter.bsp_qualification b,
                b2b_parameter.bsp_qualification_level c
            WHERE
                a.qualify_name_code = b.qualification_code AND
                a.qualify_level_code = c.level_code AND
                a.bid_announcement_uid = :biddingId
sql;
        $sqlParams[":biddingId"] = $biddingId;

        return \Yii::$app->db->createCommand($sql, $sqlParams)->queryColumn();
    }

    /**
     * 获取招标服务区域
     * @param $biddingId
     * @return array
     */
    public function getBiddingServiceAreaNamesById($biddingId)
    {
        $biddingAreaCodes = BiddingServiceAreaModel::find()
            ->select(["province_code", "city_code"])
            ->where(["bid_announcement_uid" => $biddingId])
            ->asArray()->all();

        $dictionaryService = new DictionaryService();
        $temp = [];
        foreach ($biddingAreaCodes as $biddingAreaCode) {
            if (!empty($biddingAreaCode["city_code"])) {
                $region = $dictionaryService->getRegionByCode($biddingAreaCode["city_code"]);
            } else {
                if ($biddingAreaCode["province_code"] == 'china') {
                    $region = '全国';
                } else {
                    $region = $dictionaryService->getRegionByCode($biddingAreaCode["province_code"]);
                }
            }
            if (!empty($region)) {
                if (!empty($region->name)) {
                    array_push($temp, $region->name);
                } else {
                    array_push($temp, $region);
                }
            }
        }
        return $temp;
    }
}